SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_VALI_CONT_Q03]    
/*--------------------------------------------------------*/              
/*---Empresa               OFISIS S.A.                 --*/              
/*---PROVnte               OFISIS         --*/              
/*---Sistema               Tesoreria                   --*/              
/*---M¸dulo                Empresa                     --*/              
/*---Programa              Movimientos No Migrados a Contabilidad  --*/              
/*---Script                CM_VALI_CONT_Q03.sql      --*/              
/*---Nombre SP             CM_VALI_CONT_Q03          --*/              
/*---Desarrollado por      Reynaldo Menacho Luyo       --*/              
/*---Fecha Creaci¸n        15/03/2004                  --*/              
/*---Base Datos            SQL SERVER           --*/              
/*---Versi¸n               2000                         --*/              
/*---Invoca a SP                                       --*/              
/*--------------------------------------------------------*/              
              
/*-------------------------------------------------------*/              
/*---Modificado 1 por     :   Carlos Marquez Escobar --*/              
/*---Fecha Modificaci®n   :   07/04/2005             --*/               
/*---Detalle Modificaci®n :   Filtro por Usuario     --*/              
/*-------------------------------------------------------*/              
/*-------------------------------------------------------*/              
/*---Drop Proc SP_VALI_CONT_Q03                      --*/              
/*---Grant Execute on SP_VALI_CONT_Q03 to public         --*/               
/*--- SP_VALI_CONT_Q03 '01', 2011, 1, 'S', 'N', 'N', 'N' --*/              
/*-------------------------------------------------------*/              
              
@ISCO_EMPR  TD_VC_002,            
@INNU_ANNO TD_IN_001,            
@INNU_MESE TD_IN_001,            
@ISST_CAJA TD_VC_001,              
@ISST_BANC TD_VC_001,            
@ISST_CXCB TD_VC_001,            
@ISST_CXPA TD_VC_001        
 As        
Declare            
            
@VSCO_MONE_DEFA   TD_VC_003,            
@VSCO_MONE_EXTR   TD_VC_003            
              
 CREATE TABLE #TWVALI_CONT_Q03_1            
 ( CO_CLAV      varchar(2) NULL,            
   CO_0001  varchar(20) NULL,            
   CO_0002  varchar(20) NULL,            
   CO_0003  varchar(20) NULL,            
   CO_0004  varchar(20) NULL,            
   CO_0005  varchar(20) NULL,            
   CO_0006  varchar(20) NULL,            
   CO_0007  varchar(20) NULL,          
   CO_0008  varchar(20) NULL,            
   CO_TIPO_OPER varchar(3) NULL,            
   FE_OPER datetime NULL,            
   CO_MONE  varchar(3) NULL,            
   IM_TOTA  numeric(16,4) DEFAULT 0,        
   CO_USUA_CREA varchar(8)NULL)            
            
 CREATE TABLE #TWVALI_CONT_Q03_2            
 ( CO_CLAV      varchar(2) NULL,            
   CO_0001  varchar(20) NULL,            
   CO_0002  varchar(20) NULL,            
   CO_0003  varchar(20) NULL,            
   CO_0004  varchar(20) NULL,            
   CO_0005  varchar(20) NULL,            
   CO_0006  varchar(20) NULL,            
   CO_0007  varchar(20) NULL,        
   CO_0008  varchar(20) NULL,            
   CO_TIPO_OPER varchar(3) NULL,            
   FE_OPER datetime NULL,            
   CO_MONE  varchar(3) NULL,            
   IM_TOTA  numeric(16,4) DEFAULT 0,        
   CO_USUA_CREA varchar(8) NULL)            
            
            
Select @VSCO_MONE_DEFA = CO_MONE_DEFA,             
       @VSCO_MONE_EXTR = CO_MONE_EXTR            
From   TMPARA_TESO            
Where  CO_EMPR = @ISCO_EMPR            
            
If @ISST_CAJA = 'S'             
Begin            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
  CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  Select '12', T1.CO_CAJA, NULL, T1.AA_CAJA, T1.MM_CAJA, T1.TI_MOVI, T1.NU_COMP_CAJA, '1',Max(T5.CO_UNID_CONC), Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE),     
  (PatIndex(Max(T1.TI_MOVI), 'E') - PatIndex(Max(T1.TI_MOVI), 'I')) * Max(T5.IM_MOVI), Max(T1.CO_USUA_CREA)              
  FROM  TTCAJA_ANUL T1, TCMOVI_CAJA T5, TMCAJA T2 , TXMVTO_CNTB t4            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND   YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND  T1.CO_TIPO_OPER != '998'             
  AND  T1.CO_TIPO_OPER != '999'  
  --AND  T5.TI_SITU != 'ANU'         
  AND  T1.CO_EMPR = T5.CO_EMPR            
  AND  T1.CO_CAJA = T5.CO_CAJA            
  AND  T1.AA_CAJA = T5.AA_CAJA            
  AND  T1.MM_CAJA = T5.MM_CAJA            
  AND  T1.TI_MOVI = T5.TI_MOVI            
  AND  T1.NU_COMP_CAJA = T5.NU_COMP_CAJA            
  AND  T1.CO_EMPR = T2.CO_EMPR            
  AND  T1.CO_CAJA = T2.CO_CAJA                
  AND  T4.CO_EMPR = T1.CO_EMPR            
  AND  T4.NU_CNTB_EMPR = 1            
  AND  T4.CO_UNID_CNTB = T5.CO_UNID_CONC            
  AND  T4.CO_OPRC_CNTB = SubString(T1.NU_COMP_CAJA,1,3)            
  AND  T4.NU_ANNO = YEAR  (T1.FE_ANUL)            
  AND  T4.NU_MESE = MONTH (T1.FE_ANUL)            
  AND  T4.NU_ASTO = '00000' + RIGHT(T1.NU_COMP_CAJA,5)                    
  AND  (( T1.ST_CONT = 'N' OR  NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                          WHERE  T1.CO_EMPR = T2.CO_EMPR            
              And T1.CO_TIPO_OPER = T2.CO_TIPO_OPER)  )    
  Or    (T1.ST_CONT = 'P' And T5.TI_SITU != 'EXT' And     
       NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                            Where T3.CO_EMPR = T1.CO_EMPR            
           And  T3.NU_CNTB_EMPR = 1            
                       And  T3.CO_UNID_CNTB = T5.CO_UNID_CONC            
                    And  T3.CO_OPRC_CNTB = SubString(T1.NU_COMP_CAJA,1,3)            
                 And  T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
              And  T3.NU_MESE = MONTH (T1.FE_ANUL)            
           And  T3.NU_ASTO = '00000' + RIGHT(T1.NU_COMP_CAJA,5)            
           And  T3.SI_MVTO_CNTB = 'ELI') )    
 Or    (T1.ST_CONT = 'P' And T5.TI_SITU = 'EXT' And     
       NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                            Where T3.CO_EMPR = T1.CO_EMPR            
           And  T3.NU_CNTB_EMPR = 1            
                       And  T3.CO_UNID_CNTB = T5.CO_UNID_CONC            
                    And  T3.CO_OPRC_CNTB = SubString(T1.NU_COMP_CAJA,1,3)            
                 And  T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
              And  T3.NU_MESE = MONTH (T1.FE_ANUL)            
           And  T3.NU_ASTO = '00000' + RIGHT(T1.NU_COMP_CAJA,5) ) )    
 )                    
   Group by T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.TI_MOVI, T1.NU_COMP_CAJA        
        
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT   '11',   T1.CO_CAJA, NULL, T1.AA_CAJA, T1.MM_CAJA, T1.TI_MOVI, T1.NU_COMP_CAJA, '1', T1.CO_UNID_CONC,            
 T1.CO_TIPO_OPER, T1.FE_OPER, T2.CO_MONE, (PatIndex(T1.TI_MOVI, 'I') - PatIndex(T1.TI_MOVI, 'E')) * T1.IM_MOVI,     
 T1.CO_USUA_CREA            
  FROM  TCMOVI_CAJA T1, TMCAJA T2            
  WHERE T1.CO_EMPR = @ISCO_EMPR        
  AND   MONTH (T1.FE_OPER) = @INNU_MESE            
  AND   YEAR  (T1.FE_OPER) = @INNU_ANNO            
  AND  T1.CO_TIPO_OPER != '998'             
  AND  T1.CO_TIPO_OPER != '999'             
  AND  T1.TI_SITU != 'ANU'             
  AND  T1.CO_EMPR = T2.CO_EMPR            
  AND  T1.CO_CAJA = T2.CO_CAJA            
  AND  (T1.ST_CONT = 'N' OR  NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER)            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_COMP_CAJA,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_OPER)            
                                       And   T3.NU_MESE = MONTH (T1.FE_OPER)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_COMP_CAJA,5))        
       ))            
        
End            
            
If @ISST_BANC = 'S'             
Begin            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003,     
  CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
  CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '10', T1.CO_BANC, T1.NU_CNTA_BANC, T1.AA_BNCO, T1.MM_BNCO,     
  T1.TI_MOVI_BANC, T1.NU_COMP_BANC, '2', T1.CO_UNID_CONC,            
  Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE),     
  (PatIndex(Max(T1.TI_MOVI_BANC), 'E')     
  - PatIndex(Max(T1.TI_MOVI_BANC), 'I')) * Max(T5.IM_MOVI),        
  Max(T1.CO_USUA_CREA)        
  FROM  TTBANC_ANUL T1, TCMOVI_BANC T5, TMCNTA_BANC T2 , TXMVTO_CNTB t4           
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND T1.CO_EMPR = T5.CO_EMPR            
  AND T1.CO_BANC = T5.CO_BANC            
  AND T1.CO_UNID_CONC = T5.CO_UNID_CONC      
  AND T1.NU_CNTA_BANC = T5.NU_CNTA_BANC            
  AND T1.AA_BNCO = T5.AA_BNCO            
  AND T1.MM_BNCO = T5.MM_BNCO            
  AND  T1.TI_MOVI_BANC = T5.TI_MOVI_BANC            
  AND  T1.NU_COMP_BANC = T5.NU_COMP_BANC            
  AND T1.CO_EMPR = T2.CO_EMPR            
  AND T1.CO_BANC = T2.CO_BANC            
  AND T1.NU_CNTA_BANC = T2.NU_CNTA_BANC            
  AND  T1.CO_TIPO_OPER != '998'             
  AND  T1.CO_TIPO_OPER != '999'             
  AND  T4.CO_EMPR = T1.CO_EMPR            
  AND  T4.NU_CNTB_EMPR = 1            
  AND  T4.CO_UNID_CNTB = T5.CO_UNID_CONC            
  AND  T4.CO_OPRC_CNTB = SubString(T1.NU_COMP_BANC,1,3)            
  AND  T4.NU_ANNO = YEAR  (T1.FE_ANUL)            
  AND  T4.NU_MESE = MONTH (T1.FE_ANUL)            
  AND  T4.NU_ASTO = '00000' + RIGHT(T1.NU_COMP_BANC,5)         
  AND  ( ( T1.ST_CONT = 'N' OR  NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                           WHERE T1.CO_EMPR = T2.CO_EMPR            
                                           And T1.CO_TIPO_OPER = T2.CO_TIPO_OPER) )            
  Or   (T1.ST_CONT = 'P' And T5.TI_SITU != 'EXT' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
              Where T3.CO_EMPR = T1.CO_EMPR            
              And   T3.NU_CNTB_EMPR = 1            
              And   T3.CO_UNID_CNTB = T5.CO_UNID_CONC            
              And   T3.CO_OPRC_CNTB = SubString(T1.NU_COMP_BANC,1,3)            
              And   T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
              And   T3.NU_MESE = MONTH (T1.FE_ANUL)            
              And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_COMP_BANC,5)            
              And   T3.SI_MVTO_CNTB = 'ELI') )           
Or   (T1.ST_CONT = 'P' And T5.TI_SITU = 'EXT' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T5.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_COMP_BANC,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
                                       And   T3.NU_MESE = MONTH (T1.FE_ANUL)            
                                 And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_COMP_BANC,5) ) )    
 )          
  GROUP BY   T1.CO_BANC, T1.NU_CNTA_BANC, T1.AA_BNCO, T1.MM_BNCO, T1.TI_MOVI_BANC, T1.NU_COMP_BANC, T1.CO_UNID_CONC          
          
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT  '9',  T1.CO_BANC, T1.NU_CNTA_BANC, T1.AA_BNCO, T1.MM_BNCO, T1.TI_MOVI_BANC, T1.NU_COMP_BANC,  '2',     
  T1.CO_UNID_CONC, T1.CO_TIPO_OPER, T1.FE_OPER, T2.CO_MONE, (PatIndex(T1.TI_MOVI_BANC, 'I') -     
  PatIndex(T1.TI_MOVI_BANC, 'E')) * T1.IM_MOVI, T1.CO_USUA_CREA            
  FROM  TCMOVI_BANC T1, TMCNTA_BANC T2            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_OPER) = @INNU_MESE            
  AND   YEAR  (T1.FE_OPER) = @INNU_ANNO            
  AND  T1.CO_TIPO_OPER != '998'             
  AND  T1.CO_TIPO_OPER != '999'             
  AND  T1.TI_SITU NOT IN( 'ANU' , 'EXT' )    
  AND T1.CO_EMPR = T2.CO_EMPR            
  AND T1.CO_BANC = T2.CO_BANC            
  AND T1.NU_CNTA_BANC = T2.NU_CNTA_BANC            
  AND  (T1.ST_CONT = 'N' OR  NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
       WHERE T1.CO_EMPR = T2.CO_EMPR            
            And T1.CO_TIPO_OPER = T2.CO_TIPO_OPER)            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                               And   T3.CO_OPRC_CNTB = SubString(T1.NU_COMP_BANC,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_OPER)            
                                       And   T3.NU_MESE = MONTH (T1.FE_OPER)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_COMP_BANC,5))            
       ))            
            
            
End            
            
            
If @ISST_CXCB = 'S'             
Begin            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,             
CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)           
  SELECT '32', T1.NU_AMAR, NULL,NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,            
 Max(T2.CO_TIPO_OPER), T1.FE_AMAR, Max(T3.CO_MONE), Sum(T1.IM_DETA_NEGA), Max(T1.CO_USUA_CREA)             
  FROM   TDAMAR_ANTC T1, TCAMAR_ANTC T2, TMCNTA_BANC T3            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_AMAR) = @INNU_MESE            
  AND    YEAR  (T1.FE_AMAR) = @INNU_ANNO            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.NU_AMAR = T2.NU_AMAR            
  AND    T1.FE_AMAR = T2.FE_AMAR            
  AND    T2.CO_TIPO_OPER != '998'            
  AND    T2.CO_TIPO_OPER != '999'            
  AND    T2.TI_SITU != 'ANU'            
  AND    T2.CO_BANC IS NOT NULL            
  AND    T2.CO_EMPR = T3.CO_EMPR            
  AND    T2.CO_BANC = T3.CO_BANC            
  AND    T2.NU_CNTA_BANC = T3.NU_CNTA_BANC            
            
  AND  (T1.ST_CONT = 'N' OR NOT EXISTS (SELECT T2.CO_EMPR FROM TDTIPO_OPER T2, TCAMAR_ANTC T4            
                                            WHERE  T4.CO_EMPR = T1.CO_EMPR            
                                            And    T4.NU_AMAR = T1.NU_AMAR            
                                            And    T2.CO_EMPR = T4.CO_EMPR            
                                            And    T2.CO_TIPO_OPER = T4.CO_TIPO_OPER)            
            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
           And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
                                       And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_AMAR, T1.FE_AMAR            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '33', T1.NU_AMAR, NULL,NULL,NULL,NULL,NULL , '3', T1.CO_UNID_CONC,           
 Max(T1.CO_TIPO_OPER), T1.FE_AMAR, Max(T3.CO_MONE), Sum(T2.IM_DETA_NEGA), Max(T1.CO_USUA_CREA)            
  FROM   TTAMAR_ANTA T1, TDAMAR_ANTC T2, TCAMAR_ANTC T5, TMCNTA_BANC T3            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.CO_EMPR = T5.CO_EMPR          
  AND    T1.CO_UNID_CONC = T5.CO_UNID_CONC            
  AND    T1.NU_AMAR = T5.NU_AMAR            
  AND    T1.FE_AMAR = T5.FE_AMAR            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.NU_AMAR = T2.NU_AMAR            
  AND    T1.FE_AMAR = T2.FE_AMAR            
  AND    T5.CO_BANC IS NOT NULL            
  AND    T5.CO_EMPR = T3.CO_EMPR            
  AND    T5.CO_BANC = T3.CO_BANC            
  AND    T5.NU_CNTA_BANC = T3.NU_CNTA_BANC            
            
  AND  (T1.ST_CONT = 'N' OR NOT EXISTS (SELECT T2.CO_EMPR FROM TDTIPO_OPER T2            
                                        WHERE  T2.CO_EMPR = T1.CO_EMPR            
                                          And    T2.CO_TIPO_OPER = T1.CO_TIPO_OPER)            
            
  Or   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
    And   T3.NU_MESE = MONTH (T1.FE_ANUL)            
                     And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_AMAR, T1.FE_AMAR            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '31', T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, NULL,NULL,NULL, '3', Max(T2.CO_UNID_CONC),           
 Max(T1.CO_TIPO_OPER), Max(T2.FE_EMIS), Max(T2.CO_MONE), Sum(T2.IM_TOTA), Max(T1.CO_USUA_CREA)            
  FROM   TTDOCC_ANUL T1, TMDOCU_CLIE T2            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_CLIE = T2.CO_CLIE            
  AND    T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU            
  AND    T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE        
  And    T1.NU_PROC = 0        
  AND  (T1.ST_CONT = 'N' OR NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER)            
            
  Or (             
  (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T2.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T2.NU_ASTO,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
                                       And   T3.NU_MESE = MONTH (T1.FE_ANUL)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T2.NU_ASTO,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI'      )         
       And Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T2.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T2.NU_ASTO,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
                                       And   T3.NU_MESE = MONTH (T1.FE_ANUL)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T2.NU_ASTO,5)  )         
      )        
       )        
 )                
  GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '29',  T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, NULL,NULL,NULL, '3', Max(T1.CO_UNID_CONC),           
 Max(T1.CO_TIPO_OPER), Max(T1.FE_EMIS), Max(T1.CO_MONE), Sum(T1.IM_TOTA), Max(T1.CO_USUA_CREA)            
  FROM  TMDOCU_CLIE T1            
  WHERE T1.CO_EMPR =  @ISCO_EMPR            
  AND  MONTH (T1.FE_EMIS) =  @INNU_MESE            
  AND  YEAR  (T1.FE_EMIS) = @INNU_ANNO            
  AND  T1.CO_TIPO_OPER != '998'            
  AND  T1.CO_TIPO_OPER != '999'            
  AND  T1.CO_ESTA_DOCU != 'ANU'            
  And  T1.NU_PROC = 0        
  AND  (T1.ST_CONT = 'N' OR  NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER)             
            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_ASTO,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_EMIS)            
                                       And   T3.NU_MESE = MONTH (T1.FE_EMIS)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_ASTO,5))            
      ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
  CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '25', T1.CO_CLIE, T1.NU_RENO , NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,           
Max(T1.CO_TIPO_OPER), T1.FE_RENO, Max(T1.CO_MONE), Sum( (PatIndex(T3.ST_SIGN, 'S') - PatIndex(T3.ST_SIGN, 'N')) * T2.IM_RENO_MREN),         
  Max(T1.CO_USUA_CREA)            
  FROM  TCRENO_CLIE T1, TDRENO_CLIE T2, TTDOCU_CNTB T3            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_RENO) = @INNU_MESE            
  AND   YEAR  (T1.FE_RENO) = @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.TI_SITU != 'ANU'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.CO_CLIE = T2.CO_CLIE            
  AND   T1.NU_RENO = T2.NU_RENO            
  AND   T1.FE_RENO = T2.FE_RENO            
  AND   T2.CO_TIPO_DOCU = T3.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER)            
            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_RENO,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_RENO)            
                                       And   T3.NU_MESE = MONTH (T1.FE_RENO)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_RENO,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.CO_CLIE, T1.NU_RENO, T1.FE_RENO            
            
            
 Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007,CO_0008,             
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
 SELECT '26',    T1.CO_CLIE, T1.NU_RENO , NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,           
 Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE), Sum( (PatIndex(T4.ST_SIGN, 'S') - PatIndex(T4.ST_SIGN, 'N')) * T3.IM_RENO_MREN),        
 Max(T1.CO_USUA_CREA)            
 FROM  TTRENC_ANUL T1, TCRENO_CLIE T2, TDRENO_CLIE T3, TTDOCU_CNTB T4            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.CO_CLIE = T2.CO_CLIE            
  AND    T1.NU_RENO = T2.NU_RENO            
  AND    T1.FE_RENO = T2.FE_RENO            
  AND    T2.CO_EMPR = T3.CO_EMPR            
  AND    T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND    T2.CO_CLIE = T3.CO_CLIE            
  AND    T2.NU_RENO = T3.NU_RENO            
  AND    T2.FE_RENO = T3.FE_RENO            
  AND    T3.CO_TIPO_DOCU = T4.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                          WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                          And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER)            
            
  Or     (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T2.NU_RENO,1,3)            
                                   And   T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
                                       And   T3.NU_MESE = MONTH (T1.FE_ANUL)            
 And   T3.NU_ASTO = '00000' + RIGHT(T2.NU_RENO,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.CO_CLIE, T1.NU_RENO, T1.FE_RENO            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '19',    T1.CO_CLIE, T1.NU_CANJ, NULL,NULL,NULL,NULL, '3',T1.CO_UNID_CONC,            
 Max(T1.CO_TIPO_OPER), T1.FE_CANJ, Max(T1.CO_MONE), Sum( (PatIndex(T3.ST_SIGN, 'S') - PatIndex(T3.ST_SIGN, 'N')) * T2.IM_CANJ_MCAN),        
 Max(T1.CO_USUA_CREA)           
  FROM  TCCANJ_CLIE T1, TDCANJ_CLIE T2, TTDOCU_CNTB T3            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_CANJ) = @INNU_MESE          
  AND   YEAR  (T1.FE_CANJ) = @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
         
 AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.TI_SITU != 'ANU'            
  AND   T1.CO_EMPR = T2.CO_EMPR          
        
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.CO_CLIE = T2.CO_CLIE            
  AND   T1.NU_CANJ = T2.NU_CANJ            
  AND   T1.FE_CANJ = T2.FE_CANJ            
  AND   T2.CO_TIPO_DOCU = T3.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER)            
            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_CANJ,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_CANJ)            
                                       And   T3.NU_MESE = MONTH (T1.FE_CANJ)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_CANJ,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.CO_CLIE, T1.NU_CANJ, T1.FE_CANJ            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '20',   T1.CO_CLIE, T1.NU_CANJ, NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,           
 Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE), Sum( (PatIndex(T4.ST_SIGN, 'S') - PatIndex(T4.ST_SIGN, 'N')) * T3.IM_CANJ_MCAN),        
 Max(T1.CO_USUA_CREA)            
  FROM   TTCANC_ANUL T1, TCCANJ_CLIE T2, TDCANJ_CLIE T3, TTDOCU_CNTB T4            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
 AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.CO_CLIE = T2.CO_CLIE            
  AND    T1.NU_CANJ = T2.NU_CANJ            
  AND    T1.FE_CANJ = T2.FE_CANJ            
  AND    T2.CO_EMPR = T3.CO_EMPR            
  AND    T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND    T2.CO_CLIE = T3.CO_CLIE            
  AND    T2.NU_CANJ = T3.NU_CANJ            
  AND    T2.FE_CANJ = T3.FE_CANJ            
  AND    T3.CO_TIPO_DOCU = T4.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2          
                             WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or     (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
        Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
             And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_CANJ,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_CANJ)            
     And   T3.NU_MESE = MONTH (T1.FE_CANJ)          
                        And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_CANJ,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.CO_CLIE, T1.NU_CANJ, T1.FE_CANJ            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '21',    T1.NU_AMAR, NULL,NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,           
 Max(T1.CO_TIPO_OPER), T1.FE_AMAR, Max(T1.CO_MONE), Sum(T2.IM_COBR_ORIG), Max(T1.CO_USUA_CREA)            
  FROM   TCAMAR_NCCL T1, TDAMAR_NCCL T2, TTDOCU_CNTB T3            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_AMAR) = @INNU_MESE            
  AND    YEAR  (T1.FE_AMAR) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER         
!= '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.TI_SITU != 'ANU'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.NU_AMAR = T2.NU_AMAR            
  AND    T1.FE_AMAR = T2.FE_AMAR     
  AND    T2.CO_TIDO_ORIG IS NOT NULL            
  AND    T2.CO_TIDO_ORIG = T3.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
                                       And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_AMAR, T1.FE_AMAR            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
  CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '22',   T1.NU_AMAR , NULL,NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,           
  Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE), Sum(T3.IM_COBR_ORIG) , Max(T1.CO_USUA_CREA)           
  FROM   TTAMAR_NCCA T1, TCAMAR_NCCL T2, TDAMAR_NCCL T3, TTDOCU_CNTB T4            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.NU_AMAR = T2.NU_AMAR            
  AND    T1.FE_AMAR = T2.FE_AMAR            
  AND    T2.CO_EMPR = T3.CO_EMPR            
  AND    T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND    T2.NU_AMAR = T3.NU_AMAR            
  AND    T2.FE_AMAR = T3.FE_AMAR            
  AND    T3.CO_TIDO_ORIG IS NOT NULL            
  AND    T3.CO_TIDO_ORIG = T4.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                     WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or     (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
             Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
  And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                            And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5)            
               And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_AMAR, T1.FE_AMAR            
            
  Insert Into #TWVALI_CONT_Q03_2(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)   
         
  SELECT '34',    T1.NU_CORR_MODI, NULL , NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,           
 T1.CO_TIPO_OPER, T1.FE_MODI, @VSCO_MONE_DEFA, (Select (Dbo.FN_TCFACT_CAMB_Q02 (T2.CO_MONE,             
 @VSCO_MONE_DEFA, T2.FE_EMIS, T2.IM_TOTA, 'V', 'O', 'S', @VSCO_MONE_DEFA, @VSCO_MONE_EXTR, T2.FA_TIPO_CAMB))), T1.CO_USUA_CREA            
  FROM  TDMODI_DOCU T1, TMLETR_CLIE T2            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_MODI) =  @INNU_MESE            
  AND   YEAR  (T1.FE_MODI) = @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
 AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_CLIE = T2.CO_CLIE            
  AND   T1.CO_TIPO_DOCU = 'LET'            
  AND   T1.NU_DOCU_CLIE = T2.NU_LETR_CLIE            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T2.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_CORR_MODI,1,3)            --SubString(T2.NU_CANJ,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_MODI)            
                                       And   T3.NU_MESE = MONTH (T1.FE_MODI)            
        And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_CORR_MODI,5))            
       ))            
            

            
  Insert Into #TWVALI_CONT_Q03_2(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,             
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '34',    T1.NU_CORR_MODI, NULL , NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,            
 T1.CO_TIPO_OPER, T1.FE_MODI, @VSCO_MONE_DEFA, (Select (Dbo.FN_TCFACT_CAMB_Q02 (T2.CO_MONE,             
 @VSCO_MONE_DEFA, T2.FE_EMIS, T2.IM_TOTA, 'V', 'O', 'S', @VSCO_MONE_DEFA, @VSCO_MONE_EXTR, T2.FA_TIPO_CAMB))),        
 T1.CO_USUA_CREA            
  FROM  TDMODI_DOCU T1, TMDOCU_CLIE T2, TTDOCU_CNTB T3            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_MODI) =  @INNU_MESE            
  AND   YEAR  (T1.FE_MODI) = @INNU_ANNO            
AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_CLIE = T2.CO_CLIE            
  AND   T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU            
  AND   T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE            
  AND   T2.CO_TIPO_DOCU = T3.TI_DOCU            
            
 AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
          
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
      Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_CORR_MODI,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_MODI)            
                                       And   T3.NU_MESE = MONTH (T1.FE_MODI)            
                          And  T3.NU_ASTO = '00000' + RIGHT(T1.NU_CORR_MODI,5))           
       ))            
            
            
  Insert Into #TWVALI_CONT_Q03_2(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '21',    T1.NU_AMAR, NULL,NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,           
 T1.CO_TIPO_OPER, T1.FE_AMAR, @VSCO_MONE_DEFA, (Select (Dbo.FN_TCFACT_CAMB_Q02 (T2.CO_MONE_LETR,             
 @VSCO_MONE_DEFA, T1.FE_AMAR, T2.IM_LETR, 'V', 'O', 'S', @VSCO_MONE_DEFA, @VSCO_MONE_EXTR, T1.FA_TIPO_CAMB))),        
 T1.CO_USUA_CREA            
  FROM  TCANTC_LETR T1, TDANTC_LETR T2            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_AMAR) = @INNU_MESE            
  AND   YEAR  (T1.FE_AMAR) = @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.TI_SITU != 'ANU'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.NU_AMAR = T2.NU_AMAR            
  AND   T1.FE_AMAR = T2.FE_AMAR            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
          
                                     WHERE  T1.CO_EMPR = T2.CO_EMPR            
              And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
                                       And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5))            
       ))            
            
            
  Insert Into #TWVALI_CONT_Q03_2(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,             
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '22',   T1.NU_AMAR , NULL,NULL,NULL,NULL,NULL, '3', T1.CO_UNID_CONC,           
 T1.CO_TIPO_OPER, T1.FE_ANUL, @VSCO_MONE_DEFA, (Select (Dbo.FN_TCFACT_CAMB_Q02 (T3.CO_MONE_LETR,             
 @VSCO_MONE_DEFA, T2.FE_AMAR, T3.IM_LETR, 'V', 'O', 'S', @VSCO_MONE_DEFA, @VSCO_MONE_EXTR, T2.FA_TIPO_CAMB))),        
 T1.CO_USUA_CREA            
  FROM  TTANTC_ANUL  T1, TCANTC_LETR T2, TDANTC_LETR T3            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND   YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.NU_AMAR = T2.NU_AMAR            
  AND   T1.FE_AMAR = T2.FE_AMAR            
  AND   T2.CO_EMPR = T3.CO_EMPR            
  AND   T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND   T2.NU_AMAR = T3.NU_AMAR            
  AND   T2.FE_AMAR = T3.FE_AMAR            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or     (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
      Where T3.CO_EMPR = T1.CO_EMPR            
  And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
                                  And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
 Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
 Select CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 Max(CO_TIPO_OPER), FE_OPER, CO_MONE, Isnull(Sum(Isnull(IM_TOTA, 0)), 0) , Max(CO_USUA_CREA)           
 From #TWVALI_CONT_Q03_2            
 Group By CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 FE_OPER, CO_MONE            
 End            
            
            
If @ISST_CXPA = 'S'            
Begin            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '27', T1.NU_AMAR, NULL,NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,            
 Max(T1.CO_TIPO_OPER), T1.FE_AMAR, Max(T1.CO_MONE), Sum(T2.IM_PAGO_CORI), Max(T1.CO_USUA_CREA)            
  FROM    TCAMAR_OCPR T1, TDAMAR_OCPR T2, TTDOCU_CNTB T3            
              
WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_AMAR) = @INNU_MESE            
  AND    YEAR  (T1.FE_AMAR) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.TI_SITU != 'ANU'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.NU_AMAR = T2.NU_AMAR            
  AND    T1.FE_AMAR = T2.FE_AMAR            
  AND    T2.CO_TIDO_ORIG IS NOT NULL            
  AND    T2.CO_TIDO_ORIG = T3.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
              Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
             And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
                                       And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_AMAR, T1.FE_AMAR            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '28', T1.NU_AMAR, NULL,NULL,NULL,NULL,NULL , '4', T1.CO_UNID_CONC,          
 Max(T1.CO_TIPO_OPER), T1.FE_AMAR, Max(T2.CO_MONE), Sum(T3.IM_PAGO_CORI), Max(T1.CO_USUA_CREA)            
  FROM  TTAMAR_OPAN T1, TCAMAR_OCPR T2, TDAMAR_OCPR T3, TTDOCU_CNTB T4, TXMVTO_CNTB t0         
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND   YEAR  (T1.FE_ANUL) =  @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.NU_AMAR = T2.NU_AMAR            
  AND   T1.FE_AMAR = T2.FE_AMAR            
  AND   T2.CO_EMPR = T3.CO_EMPR            
  AND   T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND   T2.NU_AMAR = T3.NU_AMAR            
  AND   T2.FE_AMAR = T3.FE_AMAR        
  AND   T3.CO_TIDO_ORIG IS NOT NULL            
  AND   T3.CO_TIDO_ORIG = T4.TI_DOCU            
        
  AND   T0.CO_EMPR = T1.CO_EMPR            
  AND   T0.NU_CNTB_EMPR = 1            
  AND   T0.CO_UNID_CNTB = T1.CO_UNID_CONC            
  AND   T0.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
  AND   T0.NU_ANNO = YEAR  (T1.FE_AMAR)            
  AND   T0.NU_MESE = MONTH (T1.FE_AMAR)            
  AND   T0.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5)        
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                           And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or     (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
                                       And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_AMAR, T1.FE_AMAR            
            
          
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
  CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '23', T1.CO_PROV, T1.NU_RENO, NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,           
  Max(T1.CO_TIPO_OPER), T1.FE_RENO, Max(T1.CO_MONE), Sum( (PatIndex(T3.ST_SIGN, 'S') - PatIndex(T3.ST_SIGN, 'N')) * T2.IM_RENO_MREN),        
  Max(T1.CO_USUA_CREA)        
           
  FROM   TCRENO_PROV T1, TDRENO_PROV T2, TTDOCU_CNTB T3            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_RENO) = @INNU_MESE            
  AND   YEAR  (T1.FE_RENO) = @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.TI_SITU != 'ANU'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.CO_PROV = T2.CO_PROV            
  AND   T1.NU_RENO = T2.NU_RENO            
  AND   T1.FE_RENO = T2.FE_RENO            
  AND   T2.CO_TIPO_DOCU = T3.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_RENO,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_RENO)            
                                       And   T3.NU_MESE = MONTH (T1.FE_RENO)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_RENO,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.CO_PROV, T1.NU_RENO, T1.FE_RENO            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,           
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '24', T1.CO_PROV, T1.NU_RENO, NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,            
 Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE), Sum( (PatIndex(T4.ST_SIGN, 'S') - PatIndex(T4.ST_SIGN, 'N')) * T3.IM_RENO_MREN),        
 Max(T1.CO_USUA_CREA)            
  FROM   TTRENP_ANUL T1, TCRENO_PROV T2, TDRENO_PROV T3, TTDOCU_CNTB T4            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) =  @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.CO_PROV = T2.CO_PROV            
  AND   T1.NU_RENO = T2.NU_RENO            
  AND   T1.FE_RENO = T2.FE_RENO            
  AND   T2.CO_EMPR = T3.CO_EMPR            
  AND   T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND   T2.CO_PROV = T3.CO_PROV            
  AND   T2.NU_RENO = T3.NU_RENO          
  AND   T2.FE_RENO = T3.FE_RENO            
  AND   T3.CO_TIPO_DOCU = T4.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or   (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_RENO,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_RENO)            
                                       And   T3.NU_MESE = MONTH (T1.FE_RENO)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_RENO,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.CO_PROV, T1.NU_RENO, T1.FE_RENO            
     
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,           
  CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '16', T1.CO_PROV, T1.NU_CANJ, NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,          
  Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE), Sum( (PatIndex(T4.ST_SIGN, 'S') - PatIndex(T4.ST_SIGN, 'N')) * T3.IM_CANJ_MCAN),         
 Max(T1.CO_USUA_CREA)           
  FROM   TTCANP_ANUL T1, TCCANJ_PROV T2, TDCANJ_PROV T3, TTDOCU_CNTB T4            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.CO_PROV = T2.CO_PROV            
  AND    T1.NU_CANJ = T2.NU_CANJ            
  AND    T1.FE_CANJ = T2.FE_CANJ            
  AND    T2.CO_EMPR = T3.CO_EMPR            
  AND    T2.CO_UNID_CONC = T3.CO_UNID_CONC            
              
  AND    T2.CO_PROV = T3.CO_PROV            
  AND    T2.NU_CANJ = T3.NU_CANJ            
  AND    T2.FE_CANJ = T3.FE_CANJ            
  AND    T3.CO_TIPO_DOCU = T4.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_CANJ,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_CANJ)            
                       And   T3.NU_MESE = MONTH (T1.FE_CANJ)            
   And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_CANJ,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.CO_PROV, T1.NU_CANJ, T1.FE_CANJ            
            
         
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '15',   T1.CO_PROV, T1.NU_CANJ, NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,             
 Max(T1.CO_TIPO_OPER), T1.FE_CANJ, Max(T1.CO_MONE), Sum( (PatIndex(T3.ST_SIGN, 'S') - PatIndex(T3.ST_SIGN, 'N')) * T2.IM_CANJ_MCAN),        
 Max(T1.CO_USUA_CREA)            
  FROM   TCCANJ_PROV T1, TDCANJ_PROV T2, TTDOCU_CNTB T3            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_CANJ) = @INNU_MESE            
  AND   YEAR  (T1.FE_CANJ) = @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.TI_SITU != 'ANU'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.CO_PROV = T2.CO_PROV            
  AND   T1.NU_CANJ = T2.NU_CANJ            
  AND   T1.FE_CANJ = T2.FE_CANJ            
  AND   T2.CO_TIPO_DOCU = T3.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_CANJ,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_CANJ)            
                                       And   T3.NU_MESE = MONTH (T1.FE_CANJ)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_CANJ,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.CO_PROV, T1.NU_CANJ, T1.FE_CANJ            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '18', T1.NU_AMAR , NULL,NULL,NULL,NULL,NULL, '4',  T1.CO_UNID_CONC,           
 Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE), Sum(T3.IM_PAGO_ORIG), Max(T1.CO_USUA_CREA)           
  FROM   TTAMAR_NCPA T1, TCAMAR_NCPR T2, TDAMAR_NCPR T3, TTDOCU_CNTB T4            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.NU_AMAR = T2.NU_AMAR            
  AND    T1.FE_AMAR = T2.FE_AMAR            
  AND    T2.CO_EMPR = T3.CO_EMPR            
  AND    T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND    T2.NU_AMAR = T3.NU_AMAR            
  AND    T2.FE_AMAR = T3.FE_AMAR            
  AND    T3.CO_TIDO_ORIG IS NOT NULL            
  AND    T3.CO_TIDO_ORIG = T4.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR           
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
                                       And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5)            
                And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_AMAR, T1.FE_AMAR            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '17', T1.NU_AMAR , NULL,NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,              
 Max(T1.CO_TIPO_OPER), Max(T1.FE_AMAR), Max(T1.CO_MONE), Sum(T2.IM_PAGO_ORIG), Max(T1.CO_USUA_CREA)            
  FROM   TCAMAR_NCPR T1, TDAMAR_NCPR T2, TTDOCU_CNTB T3            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_AMAR) = @INNU_MESE            
  AND    YEAR  (T1.FE_AMAR) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.TI_SITU != 'ANU'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND    T1.NU_AMAR = T2.NU_AMAR            
  AND    T1.FE_AMAR = T2.FE_AMAR            
  AND    T2.CO_TIDO_ORIG IS NOT NULL            
  AND    T2.CO_TIDO_ORIG = T3.TI_DOCU            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_AMAR,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_AMAR)            
                                       And   T3.NU_MESE = MONTH (T1.FE_AMAR)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_AMAR,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_AMAR, T1.FE_AMAR            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '3', T1.CO_PROV, T1.CO_TIPO_DOCU, T1.NU_DOCU_PROV , NULL,NULL,NULL, '4', Max(T1.CO_UNID_CONC),            
 Max(T1.CO_TIPO_OPER), Max(T1.FE_REGI_COMP), Max(T1.CO_MONE), Sum(T1.IM_TOTA), Max(T1.CO_USUA_CREA)            
  FROM  TMDOCU_PROV T1            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_REGI_COMP) = @INNU_MESE            
  AND   YEAR  (T1.FE_REGI_COMP) = @INNU_ANNO            
  AND   T1.CO_TIPO_OPER != '998'            
  AND   T1.CO_TIPO_OPER != '999'            
  AND   T1.CO_ESTA_DOCU != 'ANU'            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_ASTO,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_REGI_COMP)            
  And   T3.NU_MESE = MONTH (T1.FE_REGI_COMP)            
                                         
        And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_ASTO,5))            
       ))            
     
  GROUP BY T1.CO_EMPR, T1.CO_PROV, T1.CO_TIPO_DOCU, T1.NU_DOCU_PROV            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '5',   T1.CO_PROV, T1.CO_TIPO_DOCU, T1.NU_DOCU_PROV, NULL,NULL,NULL, '4', Max(T2.CO_UNID_CONC),          
 Max(T1.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T2.CO_MONE), Sum(T2.IM_TOTA), Max(T1.CO_USUA_CREA)            
  FROM   TTDOCP_ANUL T1, TMDOCU_PROV T2, TXMVTO_CNTB T0        
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND    T1.CO_TIPO_OPER != '998'            
  AND    T1.CO_TIPO_OPER != '999'            
  AND    T1.CO_EMPR = T2.CO_EMPR            
  AND    T1.CO_PROV = T2.CO_PROV            
  AND    T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU            
  AND    T1.NU_DOCU_PROV = T2.NU_DOCU_PROV            
        
 AND    T0.CO_EMPR = T1.CO_EMPR            
  AND    T0.NU_CNTB_EMPR = 1            
  AND    T0.CO_UNID_CNTB = T2.CO_UNID_CONC            
  AND    T0.CO_OPRC_CNTB = SubString(T2.NU_ASTO,1,3)            
  AND    T0.NU_ANNO = YEAR  (T1.FE_ANUL)            
  AND    T0.NU_MESE = MONTH (T1.FE_ANUL)            
  AND    T0.NU_ASTO = '00000' + RIGHT(T2.NU_ASTO,5)            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT CO_EMPR FROM TDTIPO_OPER T2            
                                       WHERE  T1.CO_EMPR = T2.CO_EMPR            
                                       And    T1.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
 Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T2.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T2.NU_ASTO,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_ANUL)            
                                       And   T3.NU_MESE = MONTH (T1.FE_ANUL)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T2.NU_ASTO,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_PROV, T1.CO_TIPO_DOCU, T1.NU_DOCU_PROV            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,             
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '1', T1.NU_REND_GAST, NULL,NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,             
 Max(T2.CO_TIPO_OPER), T1.FE_REND_GAST, Max(T3.CO_MONE), SUM(T2.IM_CONV), Max(T1.CO_USUA_CREA)             
  FROM  TCREND_GAST T1, TDREND_GAST T2, TMCNTA_BANC T3            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_REND_GAST) = @INNU_MESE            
  AND   YEAR  (T1.FE_REND_GAST) = @INNU_ANNO          
  AND   T1.TI_SITU != 'ANU'            
  AND   T2.CO_TIPO_OPER != '998'            
  AND   T2.CO_TIPO_OPER != '999'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.NU_REND_GAST = T2.NU_REND_GAST            
  AND   T1.FE_REND_GAST = T2.FE_REND_GAST            
  AND   T1.CO_BANC_DEST IS NOT NULL            
  AND   T1.CO_EMPR_DEST = T3.CO_EMPR            
  AND   T1.CO_BANC_DEST = T3.CO_BANC            
  AND   T1.NU_CNTA_DEST = T3.NU_CNTA_BANC            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT T2.CO_EMPR FROM TDTIPO_OPER T2, TDREND_GAST T3            
                                        WHERE  T1.CO_EMPR = T3.CO_EMPR            
                                        AND    T1.NU_REND_GAST = T3.NU_REND_GAST            
                                        AND    T3.CO_EMPR = T2.CO_EMPR            
                                        And    T3.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_REND_GAST,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_REND_GAST)            
                                       And   T3.NU_MESE = MONTH (T1.FE_REND_GAST)            
                                And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_REND_GAST,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_REND_GAST, T1.FE_REND_GAST            
            
              Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '1', T1.NU_REND_GAST, NULL,NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,           
 Max(T2.CO_TIPO_OPER), T1.FE_REND_GAST, Max(T3.CO_MONE), SUM(T2.IM_CONV), Max(T1.CO_USUA_CREA)            
  FROM  TCREND_GAST T1, TDREND_GAST T2, TMCAJA T3            
  WHERE T1.CO_EMPR = @ISCO_EMPR            
  AND   MONTH (T1.FE_REND_GAST) = @INNU_MESE            
  AND   YEAR  (T1.FE_REND_GAST) = @INNU_ANNO            
  AND   T1.TI_SITU != 'ANU'            
  AND   T2.CO_TIPO_OPER != '998'            
  AND   T2.CO_TIPO_OPER != '999'            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.NU_REND_GAST = T2.NU_REND_GAST            
  AND   T1.FE_REND_GAST = T2.FE_REND_GAST            
  AND   T1.CO_CAJA_DEST IS NOT NULL            
  AND   T1.CO_EMPR_DEST = T3.CO_EMPR            
  AND   T1.CO_CAJA_DEST = T3.CO_CAJA            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT T2.CO_EMPR FROM TDTIPO_OPER T2, TDREND_GAST T3            
                                        WHERE  T1.CO_EMPR = T3.CO_EMPR            
                                        AND    T1.NU_REND_GAST = T3.NU_REND_GAST            
                           AND    T3.CO_EMPR = T2.CO_EMPR            
                                        And    T3.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  OR    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_REND_GAST,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_REND_GAST)            
                                       And   T3.NU_MESE = MONTH (T1.FE_REND_GAST)            
                                       And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_REND_GAST,5))            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_REND_GAST, T1.FE_REND_GAST            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '2', T1.NU_REND_GAST , NULL,NULL,NULL,NULL,NULL, '4', T1.CO_UNID_CONC,            
 Max(T3.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T4.CO_MONE), SUM(T3.IM_CONV), Max(T1.CO_USUA_CREA)             
  FROM   TCREND_ANUL T1, TCREND_GAST T2, TDREND_GAST T3, TMCNTA_BANC T4            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.NU_REND_GAST = T2.NU_REND_GAST            
  AND   T1.FE_REND_GAST = T2.FE_REND_GAST            
  AND   T2.CO_EMPR = T3.CO_EMPR            
  AND   T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND   T2.NU_REND_GAST = T3.NU_REND_GAST            
  AND   T2.FE_REND_GAST = T3.FE_REND_GAST            
  AND   T2.CO_BANC_DEST IS NOT NULL            
  AND   T2.CO_EMPR_DEST = T4.CO_EMPR            
  AND   T2.CO_BANC_DEST = T4.CO_BANC            
  AND   T2.NU_CNTA_DEST = T4.NU_CNTA_BANC            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT T2.CO_EMPR FROM TDTIPO_OPER T2, TDREND_GAST T3            
                                        WHERE  T1.CO_EMPR = T3.CO_EMPR            
                                        AND    T1.NU_REND_GAST = T3.NU_REND_GAST            
                                        AND    T3.CO_EMPR = T2.CO_EMPR            
                                        And    T3.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_REND_GAST,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_REND_GAST)            
                                       And   T3.NU_MESE = MONTH (T1.FE_REND_GAST)            
                And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_REND_GAST,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_REND_GAST, T1.FE_REND_GAST            
            
            
  Insert Into #TWVALI_CONT_Q03_1(CO_CLAV, CO_0001, CO_0002, CO_0003, CO_0004, CO_0005, CO_0006, CO_0007, CO_0008,            
 CO_TIPO_OPER, FE_OPER, CO_MONE, IM_TOTA, CO_USUA_CREA)            
  SELECT '2', T1.NU_REND_GAST , NULL,NULL,NULL,NULL,NULL, '4',T1.CO_UNID_CONC,              
 Max(T3.CO_TIPO_OPER), Max(T1.FE_ANUL), Max(T4.CO_MONE), SUM(T3.IM_CONV), Max(T1.CO_USUA_CREA)             
  FROM   TCREND_ANUL T1, TCREND_GAST T2, TDREND_GAST T3, TMCAJA T4            
  WHERE  T1.CO_EMPR = @ISCO_EMPR            
  AND    MONTH (T1.FE_ANUL) = @INNU_MESE            
  AND    YEAR  (T1.FE_ANUL) = @INNU_ANNO            
  AND   T1.CO_EMPR = T2.CO_EMPR            
  AND   T1.CO_UNID_CONC = T2.CO_UNID_CONC            
  AND   T1.NU_REND_GAST = T2.NU_REND_GAST            
  AND   T1.FE_REND_GAST = T2.FE_REND_GAST            
  AND   T2.CO_EMPR = T3.CO_EMPR            
  AND   T2.CO_UNID_CONC = T3.CO_UNID_CONC            
  AND   T2.NU_REND_GAST = T3.NU_REND_GAST            
  AND   T2.FE_REND_GAST = T3.FE_REND_GAST            
  AND   T2.CO_CAJA_DEST IS NOT NULL            
  AND   T2.CO_EMPR_DEST = T4.CO_EMPR            
  AND   T2.CO_CAJA_DEST = T4.CO_CAJA            
            
  AND  (T1.ST_CONT = 'N' OR  ( NOT EXISTS (SELECT T2.CO_EMPR FROM TDTIPO_OPER T2, TDREND_GAST T3            
                                        WHERE  T1.CO_EMPR = T3.CO_EMPR            
                                        AND    T1.NU_REND_GAST = T3.NU_REND_GAST            
                                        AND    T3.CO_EMPR = T2.CO_EMPR         
                                        And    T3.CO_TIPO_OPER = T2.CO_TIPO_OPER))            
            
  Or    (T1.ST_CONT = 'P' And NOT Exists (Select CO_EMPR From TXMVTO_CNTB T3            
                                       Where T3.CO_EMPR = T1.CO_EMPR            
                                       And   T3.NU_CNTB_EMPR = 1            
                                       And   T3.CO_UNID_CNTB = T1.CO_UNID_CONC            
        
                                       And   T3.CO_OPRC_CNTB = SubString(T1.NU_REND_GAST,1,3)            
                                       And   T3.NU_ANNO = YEAR  (T1.FE_REND_GAST)            
                                       And   T3.NU_MESE = MONTH (T1.FE_REND_GAST)            
                   And   T3.NU_ASTO = '00000' + RIGHT(T1.NU_REND_GAST,5)            
                                       And   T3.SI_MVTO_CNTB = 'ELI')            
       ))            
            
  GROUP BY T1.CO_EMPR, T1.CO_UNID_CONC, T1.NU_REND_GAST, T1.FE_REND_GAST            
            
End        
         
Select T1.CO_CLAV, T1.CO_0001, T1.CO_0002, T1.CO_0003, T1.CO_0004, T1.CO_0005, T1.CO_0006, T1.CO_0007, T1.CO_0008,            
 T1.CO_TIPO_OPER, Convert(Varchar(10), T1.FE_OPER, 103), T1.CO_MONE, T2.DE_MONE, T1.IM_TOTA, T1.CO_USUA_CREA            
From #TWVALI_CONT_Q03_1 T1, TTMONE T2            
Where T1.CO_MONE = T2.CO_MONE            
ORDER BY T1.CO_0007, T1.CO_CLAV, T1.CO_TIPO_OPER            
            
Return        
/*------------------------------------------- FIN ---------------------------------------------------------------*/ 
GO
